<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
Class Mproject extends MY_Model
{
    public function __construct()
    {
        parent::__construct();
    }
	
	public function getProjectDetail($id){
		$data = $this->db->query('SELECT p.*, u.fullname, u.username FROM project AS p INNER JOIN user AS u ON u.id = p.user_id WHERE p.id = '.$id.' ')->row_object();
               
		$data->project_user = $this->db->query('SELECT u.id, u.username, u.fullname FROM user AS u INNER JOIN project_key AS pk ON u.id = pk.user_id WHERE pk.project_id = '.$data->id.' ')->result_object();
//		if( $this->session->userdata('level') == 1 ){
			$module = $this->db->query('SELECT * FROM module WHERE project_id = '.$data->id.' ORDER BY created ASC ')->result_object();
//		}else{    //CHO XEM TẤT CẢ CAC MODULE TRONG PROJECT
//			if( $this->session->userdata('level') == 2 ){
//		//THIẾU DUYỆT PROJECT		$module = $this->db->query('SELECT m.* FROM module AS m INNER JOIN module_key AS mk ON m.id = mk.module_id WHERE mk.user_id = '.$this->session->userdata('admin_id').' ORDER BY m.created DESC')->result_object();
//			}
//		}
//                echo "<pre>";var_dump($module);die;
		foreach ( $module AS $el1=>$le1 ){
			$module[$el1]->module_user = $this->db->query('SELECT u.fullname,u.username,u.id,mk.rate,mk.status FROM module_key AS mk INNER JOIN user AS u ON u.id = mk.user_id WHERE mk.module_id = '.$le1->id.' ')->result_object();
		$module[$el1]->comment=$this->db->query("SELECT p.*,u.fullname FROM module_key_comment as p INNER JOIN user as u ON p.user_id=u.id WHERE p.module_id = '$le1->id' ORDER BY p.created DESC ")->result_object();
		$module[$el1]->reason=$this->db->query("SELECT * FROM module_key_reason as m WHERE m.module_id='$le1->id' ORDER BY m.created DESC LIMIT 1 ")->row()->reason;
			$total_rate1 = $this->db->query('SELECT SUM(rate) AS rate FROM module_key WHERE module_id = '.$le1->id.' ')->row_object()->rate;
			$num_rate1 = $this->db->query('SELECT id FROM module_key WHERE module_id = '.$le1->id.' ')->num_rows();
			$module[$el1]->percent = ceil($total_rate1/$num_rate1);
                        
//		var_dump($module[$el1]->reason,"SELECT * FROM module_key_reason as m WHERE m.module_id='$le1->id' ORDER BY m.created DESC LIMIT 1");	
			
		}
//                die;
		$data->module = $module;
			$total_rate = $this->db->query('SELECT SUM(module_key.rate) AS rate FROM module_key INNER JOIN module ON module.id=module_key.module_id WHERE module.project_id = '.$data->id.' ')->row_object()->rate;
			$num_rate = $this->db->query('SELECT module_key.id FROM module_key INNER JOIN module ON module.id=module_key.module_id WHERE module.project_id = '.$data->id.' ')->num_rows();

			$data->percent = ceil($total_rate/$num_rate);
            $data->time_percent = ceil( (time()- strtotime($data->time_start))/(strtotime($data->time_end) - strtotime($data->time_start)) )*100;
		
		return $data;
	}
    
    public function getUserList()
    {
        $users = $this->db->query("SELECT id, fullname AS name FROM user WHERE level = 2 ")->result_array();
        
        return $users;
    }
    
    public function addProject($data,$users)
    {
        $data['user_id'] = $this->session->userdata('admin_id');
        $data['created'] = date('Y/m/d H:i:s');
        $data['active'] = 1;
        
        $check = $this->db->query("SELECT id FROM project WHERE project_name = '".$data['project_name']."'")->num_rows();
        
        if($check == 0){
            $this->db->insert('project', $data);
            $id = $this->db->insert_id();
            
            $this->insertPjKey($id,$users);
            return $id;
        }
        return 201;
    }
    
    /**
     * Thêm tiến độ 0% trước vào bảng avg
     * 
     * @param int $id    id project
     * @param array $data       chi tiết project
     * @param array $users      mảng user thêm vào project
     */
    public function addAvg($id,$data,$users){
        
        //Lấy ngày giữa 2 ngày : ví dụ 4/6 và 6/6 trả về 4/6,5/6,6/6
        $date1 = date("d/m/Y",  strtotime($data['time_start']));
        $date2 = date("d/m/Y",  strtotime($data['time_end']));
        $datePeriod = $this->returnDates($date1, $date2);  //ngày truyền vào hàm là d/m/Y
        $arrayBetween = array();
        foreach ($datePeriod as $date) {
            $arrayBetween[] = $date->format('Y-m-d');
        }
//        var_dump($users);die;
        //Insert vào bảng avg..
        foreach($users AS $key => $user){
            foreach ($arrayBetween as $key => $dateInsert )
                {
                        $this->db->insert("avg_user_project",array(
                        'rate_avg'=>0,
                        'project_id'=>$id,
                        'date_save' => $dateInsert,
                        'user_id'=>$user,  
                        ));
                } 
        }
        return 200;
    }
    
    /**
     * Sửa tiến độ vào bảng tiến độ avg
     * 
     * @param int $id    id project
     * @param array  $data       chi tiết project
     * @param array  $users      mảng user thêm vào project
     * @param object $arrDate    ngày bắt đầu và kết thúc cũ trước khi sửa
     */
    public function editAvg($id,$data,$users,$arrDate){
        
        //user cũ
        $arrUser=$this->db->query("SELECT user_id FROM project_key WHERE project_id = '$id'")->result_object();
        $arrUserOld=array();
        foreach ($arrUser as $value)
        {
            $arrUserOld[]=$value->user_id;
        }
        
        //Lấy user cũ-user mới=user delete
        $userDelete=  array_diff($arrUserOld, $users);
        //Lấy user mới-user cũ=user insert
        $userInsert=  array_diff($users, $arrUserOld);
        
//        echo '<pre>';var_dump($arrUserOld,$users,$userDelete,$userInsert);die;
        //Lấy ngày giữa 2 ngày (bản sửa)
        $date1 = date("d/m/Y",  strtotime($data['time_start']));
        $date2 = date("d/m/Y",  strtotime($data['time_end']));
        $datePeriod = $this->returnDates($date1, $date2);  //ngày truyền vào hàm là d/m/Y
        $arrayBetweenNew = array();
        foreach ($datePeriod as $date) {
            $arrayBetweenNew[] = $date->format('Y-m-d');
        }
        
        //Lấy ngày giữa 2 ngày(bản cũ trước khi sửa)
        $date3 = date("d/m/Y",  strtotime($arrDate->time_start));
        $date4 = date("d/m/Y",  strtotime($arrDate->time_end));
        $datePeriod = $this->returnDates($date3, $date4);  //ngày truyền vào hàm là d/m/Y
        $arrayBetweenOld = array();
        foreach ($datePeriod as $date) {
            $arrayBetweenOld[] = $date->format('Y-m-d');
        }
//        echo '<pre>';var_dump($arrayBetweenNew,$arrayBetweenOld); 
       //Lấy mảng ngày mới -ngày cũ = những ngày thêm mới so với ngày cũ
       $dateInsertNew= array_diff($arrayBetweenNew, $arrayBetweenOld);
       //Lấy mảng ngày cũ -ngày mới = những ngày bị bỏ đi so với ngày mới
       $dateDeleteNew= array_diff($arrayBetweenOld, $arrayBetweenNew);
       //Những ngày giao nhau giữa cũ và mới
       $dateIntersec=  array_diff($arrayBetweenOld, $dateDeleteNew);
//       echo '<pre>';var_dump($dateInsertNew,$dateDeleteNew); die;
        
//        var_dump($users);die;
        //Insert những ngày mới thêm(so với cũ) và mảng user mới vào bảng avg..
        foreach($users AS $key => $user){
            foreach ($dateInsertNew as $key => $dateInsert )
                {
                        $this->db->insert("avg_user_project",array(
                        'rate_avg'=>0,
                        'project_id'=>$id,
                        'date_save' => $dateInsert,
                        'user_id'=>$user,  
                        ));
                } 
        }
        
        //Delete những ngày đã bỏ trong bảng avg
        if(!empty($dateDeleteNew))
        {
            foreach ($dateDeleteNew as $value )
            {
                    $this->db->delete('avg_user_project', array('date_save' => $value,'project_id'=>$id));
            }
        }
        
        //Những ngày ở giữa (giao nhau giữa mảng date cũ và mảng date mới)
        //Những user trong những ngày ở giữa này thay đổi,xóa những user đã bỏ đi,thêm mới user mới trong ngày ở giữa này.
        //Delete những user không trong dự án nữa
        if(!empty($userDelete))
        {
            foreach ($userDelete as $value )
            {
                    $this->db->delete('avg_user_project', array('user_id' => $value,'project_id'=>$id));
            }
        }
        
        
        //Insert những user mới vào trong khoảng thời gian giao nhau cũ và mới
        foreach($userInsert AS $key => $user){
            foreach ($dateIntersec as $key => $dateInsert )
                {
                        $this->db->insert("avg_user_project",array(
                        'rate_avg'=>0,
                        'project_id'=>$id,
                        'date_save' => $dateInsert,
                        'user_id'=>$user,  
                        ));
                } 
        }
        return 200;
    }
    
    public function getProjectInfo($id)
    {
        $data = $this->db->query("SELECT * FROM project WHERE id = ".$id)->row_object();
        $data->users = $this->db->query("SELECT u.id, u.fullname AS name FROM user AS u LEFT JOIN project_key AS k ON k.user_id = u.id WHERE project_id = ".$id)->result_array();
        
        return $data;
    }
	
    public function editProject($id,$data,$users)
    {
        $data['user_id'] = $this->session->userdata('admin_id');
        $data['created'] = date('Y/m/d H:i:s');
        $data['active'] = 1;
        
        $check = $this->db->query("SELECT id FROM project WHERE project_name = '".$data['project_name']."' AND id != ".$id)->num_rows();
        
        if($check == 0){
            $this->db->update('project', $data, "id = ".$id);
            
            $select_user = $this->db->query("SELECT user_id FROM project_key WHERE project_id = ".$id)->result_object();
            $users_old = array();
            foreach($select_user AS $key => $value){
                $users_old[] = $value->user_id;
            }
            
			//echo 'users_old<pre>';die(print_r($users_old));
			//echo 'users<pre>';die(print_r($users));
			
            $diff = array_diff($users_old, $users);
            
            foreach ($diff as $key => $value)
            {
                     $this->db->query("DELETE FROM module_key WHERE project_id = '$id' AND user_id = '$value'");
            }
//            var_dump($diff);die;
			foreach($diff AS $key => $value){
				$arr = array(
					'user_id' => $value,
					'project_id' => $id
				); 
				$this->db->delete('project_key', $arr);
			}
			
			$diff2 = array_diff($users, $users_old);
			foreach($diff2 AS $key => $value){
				$arr = array(
					'user_id' => $value,
					'project_id' => $id
				); 
				$this->db->insert('project_key', $arr);
			}
			return 200;                
        }

        return 201;
    }
	
    private function insertPjKey($id,$users){
        foreach($users AS $key => $value){
            $arr = array(
                'user_id' => $value,
                'project_id' => $id
            ); 
            $this->db->insert('project_key', $arr);
            $projectDetail=$this->db->query("Select * FROM project WHERE id = '$id'")->row();
            $this->sendMailUser($value, $projectDetail);
        }
        
        return 200;
    }
    
     public function sendMailUser($user_id,$moDetail)
    {
        $user = $this->db->query('SELECT * FROM user WHERE id = '.$user_id.' LIMIT 1')->row();
        $idProject=$moDetail->id;
        $projectName=$moDetail->project_name;
        $config = array(
						'protocol' => 'smtp',
						'smtp_host' => $this->_smtp_host,
						'smtp_user' => $this->_smtp_user,
						'smtp_pass' => $this->_smtp_pass,
						'mailtype' => 'html'
                    );
        $this->load->library('email');
        $this->email->initialize($config);
        $body= "Bạn mới được tham gia vào project ".$projectName."<br>";
        $body.= "Nội dung: ".$moDetail->content."<br>";
        $body.= "Thời gian thực hiện: ".date('d/m/Y',  strtotime($moDetail->time_start))." - ".date('d/m/Y',  strtotime($moDetail->time_end))."<br>";
        $body.= "Link Project : "."<a href='".site_url()."project-detail/".$idProject."'>".site_url()."project-detail/".$idProject."</a>"."<br>";
     
        $this->email->from($this->_smtp_user, 'Wicity');
        $this->email->to($user->email);  
        $this->email->subject($moDetail->project_name);
        $this->email->message($body);    
        
        $a=$this->email->send();
    }
    
	public function savePinProject($id){
		$pin = $this->db->query('SELECT * FROM project WHERE id = '.$id.' ')->row()->is_pin;
		if( !in_array($pin,array('',null)) && $pin == 1 ){
			$this->db->update('project',array('is_pin' => 0));
		}else{
			$this->db->update('project',array('is_pin' => 1));
		}
		return 200;
	}
    
    public function getListProject($number = 0,$offset = 0)
    {
        $user_id=$this->session->userdata("admin_id");
        $limit = $offset ? intval($offset) : '0';
        if($this->session->userdata('level') != 1) {$sqlx="AND pk.user_id = '$user_id'";}
        else{$sqlx = ''; }
        $sql = "SELECT p.*,DATE_FORMAT(p.created,'%d/%m/%Y %T') as created1,DATE_FORMAT(p.time_start,'%d/%m/%Y %T') as start,DATE_FORMAT(p.time_end,'%d/%m/%Y %T') as end,u1.username as user_created,GROUP_CONCAT(u.username SEPARATOR ', ') as users
                FROM project as p
                INNER JOIN project_key as pk ON p.id = pk.project_id 
                INNER JOIN user as u ON pk.user_id = u.id
                INNER JOIN user as u1 ON u1.id = p.user_id
                WHERE p.active = 1 ".$sqlx."
                GROUP BY p.id ";
        $sql .= " ORDER BY p.created DESC";
        $count = $this->db->query($sql)->num_rows();
        
        $xdata = " LIMIT ".$limit.",".$number." ";
        
        $xs = $this->db->query($sql.$xdata)->result_object();
        return array('data' => $xs, 'count' => $count);
    }
    
    public function getReportProject($id){
		$data = $this->db->query("SELECT p.*,u.fullname,u.username FROM project AS p INNER JOIN user AS u ON u.id = p.user_id WHERE p.id = '$id' ORDER BY p.is_pin DESC, p.created DESC ")->result_object();
		foreach ($data AS $el=>$le){
   
			if( !in_array($le->time_start,array('',null)) && !in_array($le->time_end,array('',null)) ){
				$le->time_percent = ceil((time() - strtotime($le->time_start))/(strtotime($le->time_end) - strtotime($le->time_start))*100);
				if( $le->time_percent >= 100 ){
					$le->time_percent = 100;
				}
			}else{
				$le->time_percent = 0;
			}
			$user = $this->db->query('SELECT u.id,u.username,u.fullname FROM user AS u INNER JOIN project_key AS pk ON u.id = pk.user_id WHERE pk.project_id =  '.$le->id.' ')->result_object();
			$le->user = $user;
			$total_rate = $this->db->query('SELECT SUM(rate) AS rate FROM module_key WHERE project_id = '.$le->id.' ')->row_object()->rate;
			$num_rate = $this->db->query('SELECT id FROM module_key WHERE project_id = '.$le->id.' ')->num_rows();
			$le->percent = ceil($total_rate/$num_rate);
			
			$user_rate = $this->db->query('SELECT DISTINCT mk.user_id as user_id, u.display_name as fullname FROM module_key as mk inner join user as u on u.id = mk.user_id WHERE mk.project_id = '.$le->id.' ')->result_object();
			foreach ( $user_rate AS $el2=>$le2 ){
				$total_rate2 = $this->db->query('SELECT SUM(rate) AS rate FROM module_key WHERE project_id = '.$le->id.' AND user_id = '.$le2->user_id.' ')->row_object()->rate;
				$number2 = $this->db->query('SELECT id FROM module_key WHERE project_id = '.$le->id.' AND user_id = '.$le2->user_id.' ')->num_rows();
				$user_rate[$el2]->rate2 = ceil($total_rate2/$number2);
			}
			$le->user_rate = $user_rate;
//                        echo "<pre>";var_dump($le);die;
		}
		return $data;
	}
        
    public function addCommentP($idUser,$projectId,$comment)
    { 
        if($projectId == null )return 201;  //không tồn tại project
        
        $check=$this->db->query("SELECT * FROM project WHERE id = '$projectId'");
        if($check->num_rows()==0)return 201;  //không tồn tại project
            
        if($this->session->userdata("level") != 1)  //Nếu không phải admin
        {
            $check=$this->db->query("SELECT * FROM project_key WHERE project_id = '$projectId' AND user_id='$idUser' ");
            if($check->num_rows()==0)return 201;  //user không thuộc project
        }
        
        $arr = array(
                'user_id'       =>  $idUser,
                'project_id'    =>  $projectId,
                'comment'       =>  $comment,
                'created'       =>  date("Y-m-d H:i:s")
            ); 
        $this->db->insert('project_key_comment', $arr);
        return 200;
            
          
    }
    
    public function addCommentM($idUser,$project_id,$comment,$module_id)
    { 
        if($project_id == null )return 201;  //không tồn tại project
        
        $check=$this->db->query("SELECT * FROM project WHERE id = '$project_id'");
       
        if($check->num_rows()==0)return 201;  //không tồn tại project
            
        if($this->session->userdata("level") != 1)  //Nếu không phải admin
        {
            $check=$this->db->query("SELECT * FROM module_key WHERE module_id = '$module_id' AND user_id='$idUser'");
            if($check->num_rows()==0)return 201;  //user không thuộc module
        }
        
        $arr = array(
                'user_id'       =>  $idUser,
                'module_id'    =>  $module_id,
                'comment'       =>  $comment,
                'created'       =>  date("Y-m-d H:i:s")
            ); 
       
        $this->db->insert('module_key_comment', $arr);
        return 200;
            
          
    }
    
    public function getCommentP($id)
    {
        return $this->db->query("SELECT p.*,u.fullname FROM project_key_comment as p INNER JOIN user as u ON p.user_id=u.id WHERE p.project_id = '$id' ORDER BY p.created DESC ")->result_object();
    }
}
